After demo building and testing features out within Azure SQL Database I like to delete the databases to save costs. Very rarely the following happens when trying to delete a database within Azure.

The Error

Failed to delete the database: TestDB. ErrorCode: 400 ErrorMessage: Database ‘TestDB’ cannot be deleted because it is used as a sync metadata database which still contains sync groups and/or sync agents.

After some digging around I remembered that I tried to setup a data sync group where this database was involved in bi-directional synchronization with another Azure SQL Database. The other database was deleted a long time ago and clicking on the sync option within the portal confirmed that nothing was actually ever connected or ready.

So I connected to the database via SSMS (SQL Server Management Studio) and had a look around, quite clearly there are sync based objects within my database with the tables naming convention as DataSync.XXXX, DSS.XXXX, TaskHosting.XXXX

Also many DSS. Stored procedures.

Also many scalar functions.

Documentation for this feature is not very detailed and there is no section on “cleaning up” / removing sync groups (even though it is simple). This being a test server I decided (rash decision) to try removing the tables first which proved to be the wrong decision (see later). To remove data sync groups all you need to do is click into the group and delete it which we will see later on. Yes, I admit, I made a bad mistake here.

So I repeat, to cleanly remove sync groups you: Click into the group and delete it…

Anyways, this is what I did before knowing about the correct and simple way. Below is a list of the tables involved with this feature which were deleted.

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables
WHERE schema_id  1

 DROP TABLE [TaskHosting].[MetaInformation]
DROP TABLE [TaskHosting].[Job]
DROP TABLE [TaskHosting].[MessageQueue]
DROP TABLE [TaskHosting].[Schedule]
DROP TABLE [TaskHosting].[ScheduleTask]
DROP TABLE [DataSync].[schema_info_dss]
DROP TABLE [DataSync].[scope_info_dss]
DROP TABLE [DataSync].[scope_config_dss]
DROP TABLE [dss].[subscription]
DROP TABLE [DataSync].[provision_marker_dss]
DROP TABLE [dss].[task]
DROP TABLE [dss].[userdatabase]
DROP TABLE [dss].[scaleunitlimits]
DROP TABLE [dss].[ScheduleTask]
DROP TABLE [dss].[taskdependency]
DROP TABLE [dss].[syncgroup]
DROP TABLE [dss].[agent_instance]
DROP TABLE [dss].[UIHistory]
DROP TABLE [dss].[EnumType]
DROP TABLE [dss].[action]
DROP TABLE [dss].[MetaInformation]
DROP TABLE [dss].[agent_version]
DROP TABLE [dss].[agent]
DROP TABLE [dss].[configuration]
DROP TABLE [dss].[syncgroupmember]

 

Removing Sync Groups – The right way

The common message from this post? How to properly remove sync groups.  Click into the group where then you will be given a delete option.

The outcome you should now get.

Morale of this post – Do not “jump the gun” and do something as rash as deleting tables /objects if you do not know / understand the ramifications of such an action. I have truly learnt a lesson here. I thought I would share this mistake, do not judge me too harshly and I am very thankful for Azure’s Built-in backups!

Thanks for reading.